﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Data.Odbc;

namespace MySqlConnection
{
    public class DBContainer : IDisposable
    {
        private const string MyConString = "SERVER=localhost;DATABASE=db_home;UID=root;";
        private MySql.Data.MySqlClient.MySqlConnection _connection;
        private static DBContainer instance = null;
        
        public MySql.Data.MySqlClient.MySqlConnection Connection
        {
            get { return _connection; }
        }

        private DBContainer()
	    {
            this._connection = new MySql.Data.MySqlClient.MySqlConnection(MyConString);
        }

        public static DBContainer GetInstance()
        {

            if (instance == null)
            {
                instance = new DBContainer();
            }
            return instance;
        }

        public void CreateImage(string name, string desc, string createDate, string ext, byte[] data, int size)
        {
            string req = "insert into images (name,description, created, extension, data, size) "
                                                 + "values (@name, @description, @created, @extension, @data, @size)";
            try
            {
                if (Connection.State != ConnectionState.Open)
                    Connection.Open();
                using (MySqlCommand command = new MySqlCommand(req, Connection))
                {
                    command.Parameters.Add(new MySqlParameter("@name", name));
                    command.Parameters.Add(new MySqlParameter("@description", desc));
                    command.Parameters.Add(new MySqlParameter("@created", createDate));
                    command.Parameters.Add(new MySqlParameter("@extension", ext));
                    MySqlParameter dataParam = new MySqlParameter("@data", MySqlDbType.Binary);
                    dataParam.Value = data;
                    command.Parameters.Add(dataParam);
                    command.Parameters.Add(new MySqlParameter("@size", size));
                    command.Prepare();
                    command.ExecuteNonQuery();
                }
            }
            catch (MySqlException ex)
            {

            }
            finally
            {
                Connection.Close();
            }
        }

        public void UpdateImage(string id, string name, string desc)
        {
            string req = "update images set name = @name, description = @description where id=@id";
            try
            {
                if (Connection.State != ConnectionState.Open)
                    Connection.Open();
                using (MySqlCommand command = new MySqlCommand(req, Connection))
                {
                    command.Parameters.Add(new MySqlParameter("@id", id));
                    command.Parameters.Add(new MySqlParameter("@name", name));
                    command.Parameters.Add(new MySqlParameter("@description", desc));
                    command.Prepare();
                    command.ExecuteNonQuery();
                }
            }
            catch (MySqlException ex)
            {

            }
            finally
            {
                Connection.Close();
            }
        }

        public void CreateImage2()
        {
            //DataSet ds = new DataSet("images_ds");
            //OdbcDataAdapter tempAP = new OdbcDataAdapter(MyConString, MyConString);
            //OdbcCommandBuilder objCommand = new OdbcCommandBuilder(tempAP);
            //tempAP.Fill(ds, SessionConstants.SessionListImages);
        }

        public ImageClass GetImageById(string id)
        {
            ImageClass img = new ImageClass();
            try
            {
                if (Connection.State != ConnectionState.Open)
                    Connection.Open();

                string req = "select * from images where id = '" + id + "'";
                using (MySqlCommand command = new MySqlCommand(req, Connection))
                {
                    using (MySqlDataReader rd = command.ExecuteReader())
                    {
                        while (rd.Read())
                        {
                            img.id = rd.GetValue(0).ToString();
                            img.name = rd.GetValue(1).ToString();
                            img.description = rd.GetValue(2).ToString();
                            img.data = (byte[])rd.GetValue(5);
                            img.ext = rd.GetValue(4).ToString();
                            img.size = rd.GetInt32(6);
                        }
                    }
                }
            }
            catch (MySqlException ex)
            {
            }
            finally
            {
                Connection.Close();
            }
            return img;
        }

        public List<ImageClass> GetAllImages()
        {
            List<ImageClass> allImgs = new List<ImageClass>();
            string req = "select * from images";
            try
            {
                if (Connection.State != ConnectionState.Open)
                    Connection.Open();
                using (MySqlCommand command = new MySqlCommand(req, Connection))
                {
                    using (MySqlDataReader rd = command.ExecuteReader())
                    {
                        while (rd.Read())
                        {
                            ImageClass img = new ImageClass();
                            img.id = rd.GetValue(0).ToString();
                            img.name = rd.GetValue(1).ToString();
                            img.description = rd.GetValue(2).ToString();
                            img.data = (byte[])rd.GetValue(5);
                            img.ext = rd.GetValue(4).ToString();
                            img.size = Int32.Parse(rd.GetValue(6).ToString());
                            allImgs.Add(img);
                        }
                    }
                }
            }
            catch (MySqlException ex)
            {
            }
            finally
            {
                Connection.Close();
            }
            return allImgs;
        }

        public void Dispose()
        {
            throw new NotImplementedException();
        }
    }
}
